<?php

namespace app\admin\controller\report;

use app\common\controller\Backend;
use think\Db;
use think\Request;

/**
 * 测试管理
 *
 * @icon fa fa-circle-o
 */
class Yunfei extends Backend
{
    
    /**
     * Test模型对象
     * @var \app\admin\model\Test
     */
    protected $model = null;

    public function _initialize()
    {
        parent::_initialize();
        $this->model = new \app\admin\model\Test;

    }
    
    /**
     * 默认生成的控制器所继承的父类中有index/add/edit/del/multi五个基础方法、destroy/restore/recyclebin三个回收站方法
     * 因此在当前控制器中可不用编写增删改查的代码,除非需要自己控制这部分逻辑
     * 需要将application/admin/library/traits/Backend.php中对应的方法复制到当前控制器,然后进行修改
     */


    public function index()
    {
        $sql="SELECT  coalminename,coalfieldname ,count(id) as cishu  FROM `mt_freightprice` GROUP BY COALMINENAME,COALFIELDNAME order by cishu desc, COALMINENAME desc  ";
        $list=db("freightprice")->query($sql);
       // dump($list);

        $this->assign("riqi",[]);
        $this->assign("list",[]);
        return $this->fetch();
    }

    public function getnum()
    {
        //当前页
        $page = $this->request->request("pageNumber");
        //分页大小
        $pagesize = $this->request->request("pageSize");

        $sql="SELECT  coalmineid,coalfieldid,coalminename,coalfieldname ,count(id) as cishu  FROM `mt_freightprice` GROUP BY COALMINENAME,COALFIELDNAME order by cishu desc, COALMINENAME desc   ";
        $list=db("freightprice")->query($sql);

        $mylist=[];
        foreach ($list as $k=>$v)
        {

            $id=$v['coalmineid'].'-'.$v['coalfieldid'];
            $tp['id']=$id;
            $tp['name']=$v['coalminename'].'-'.$v['coalfieldname'].'-'.$v['cishu'];
            $mylist[]=$tp;

        }

        $pagelist=[];
        for($j=($page-1)*$pagesize;$j<$page*$pagesize;$j++)
        {
            $pagelist[]=$mylist[$j];
        }

        $res['list']=$pagelist;
        $res['total']=ceil( count($mylist)/$pagesize);

        // dump($list);
        return json($res);

    }




    public function test()
    {

        if ($this->request->isAjax()) {

            //如果发送的来源是Selectpage，则转发到Selectpage
            if ($this->request->request('keyField')) {
                return $this->selectpage();
            }
            $luxian=input("xianlu");
            $a=explode("-",$luxian);
            if(count($a)!=2) $this->error("线路错误!");

            $map=[];
            if($a)
            {
                $map['coalmineid']=$a[0];
                $map['coalfieldid']=$a[1];
            }

            $riqi=input("riqi");
            if($riqi)
            {
                $b=explode(" - ",$riqi);
                if(count($b)!=2) $this->error("日期错误");
                if($b)
                {
                    $map['usedate']=['between',$b];
                }

            }

            $list = db("freightprice")->field(" COALMINENAME,COALFIELDNAME,CURRPRICE as price,USEDATE as riqi")->where($map)->order("USEDATE asc")->select();

            $riqi=[];$price=[];
            foreach($list as $k=>$v)
            {
                $triqi=strtotime($v['riqi']);

                $riqi[]=date("y/m/d",$triqi);
                $price[]=$v['price'];
				$title=$v['COALMINENAME'].'-'.$v['COALFIELDNAME'];
            }

            $fudu=max($price)-min($price);
            $res['riqi']=$riqi;
            $res['showdata']=$price;
            $res['title']=$title;
            $res['fudu']=$fudu;

            //dump($price);die("87");
            //$list = collection($list)->toArray();

            return json($res);
        }

        return $this->fetch();
    }


    public function getmeinum()
    {

        //当前页
        $page = $this->request->request("pageNumber");
        //分页大小
        $pagesize = $this->request->request("pageSize");

        $sql="SELECT  coaltypename ,count(1) as cishu  FROM `mt_contract_item` WHERE   `CTTID` IN ( SELECT `cttid` FROM `mt_contract` WHERE  `CTTTYPE` = 'buy' and CTTTYPE2='COAL' )  GROUP BY coaltypename order by cishu desc, coaltypename desc   ";
        $list=db()->query($sql);

        $mylist=[];
        foreach ($list as $k=>$v)
        {

            $id=$v['coaltypename'] ;
            $tp['id']=$id;
            $tp['name']=$id.'-'.$v['cishu'];
            $mylist[]=$tp;

        }

        $pagelist=[];
        for($j=($page-1)*$pagesize;$j<$page*$pagesize;$j++)
        {
            $pagelist[]=$mylist[$j];
        }

        $res['list']=$pagelist;
        $res['total']=ceil( count($mylist)/$pagesize);

        // dump($list);
        return json($res);

    }

    public function meijia()
    {

        if ($this->request->isAjax()) {

            //如果发送的来源是Selectpage，则转发到Selectpage
            if ($this->request->request('keyField')) {
                return $this->selectpage();
            }
            $meizhong=input("xianlu");
            $map=[];
            if($meizhong)
            {
                $map['coaltypename']=$meizhong;
            }

            $riqi=input("riqi");
            if($riqi)
            {
                $b=explode(" - ",$riqi);
                if(count($b)!=2) $this->error("日期错误");
                if($b)
                {
                    $map['INTPUTDATE']=['between',$b];
                }

            }

            $list = db("contract_item")->field("CURRPRICE as price,INTPUTDATE as riqi")->where($map)
                ->where(
                    "CTTID", 'in', function ($query) {
                    $query->table('mt_contract')->where('CTTTYPE', 'buy')->where('CTTTYPE2','COAL')->field('cttid');
                }

                )
                ->order("INTPUTDATE asc")->select();
				//die(db()->getlastsql());
           // die($list);

            $riqi=[];$price=[];
            foreach($list as $k=>$v)
            {
                $triqi=strtotime($v['riqi']);

                $riqi[]=date("y/m/d",$triqi);
                $price[]=$v['price'];
            }

            $fudu=max($price)-min($price);


            $res['riqi']=$riqi;
            $res['showdata']=$price;
            $res['title']=$meizhong;
            $res['fudu']=$fudu;

            //dump($price);die("87");
            //$list = collection($list)->toArray();

            return json($res);
        }


        return $this->fetch();
    }


    public function fayun()
    {

        if ($this->request->isAjax()) {


            $luxian=input("xianlu");
            $a=explode("-",$luxian);
            if(count($a)!=2) $this->error("线路错误!");

            $map=[];
            if($a)
            {
                $map['coalmineid']=$a[0];
                $map['coalfieldid']=$a[1];
            }

            $riqi=input("riqi");
            $start=date("Y-m-d",strtotime("-36 month"));
            $end=date("Y-m-d" );
            if($riqi)
            {
                $b=explode(" - ",$riqi);
                if(count($b)!=2) $this->error("日期错误");
                $start=date("Y-m-d",strtotime($b[0]));
                $end=date("Y-m-d",strtotime($b[1]));

                if($b)
                {
                    $map['coalminezcdate']=['between',$b];
                }

            }
            else
            {
                $map['coalminezcdate']=['between',[$start,$end]];
            }

          /*  $list = db("rxzc")->field("COALMINENAME ,RKCOALFIELD,sum(JWEIGHT) as fayun, zcdate as riqi")
                ->group("  to_days(ZCDATE)")
                ->where($map)->order("ZCDATE asc  ")
               //->buildsql();
                ->select();
              //die($list);

          SELECT   COALMINEID,COALFIELDID,COALMINENAME,FIELDNAME,CONCAT(year(coalminezcdate),'-',month(coalminezcdate) ) as yuefen ,sum(jweight) as fayun ,
year(coalminezcdate) as fyear,month(coalminezcdate) as fmonth

from  `mt_coaltransrec`  group by COALMINEID,COALFIELDID, yuefen
order by fyear asc,fmonth asc


*/

			$list = db("coaltransrec")
                ->field("COALMINEID,COALFIELDID,COALMINENAME,FIELDNAME,CONCAT(year(coalminezcdate),'-',month(coalminezcdate) ) as riqi ,sum(jweight) as fayun ,
year(coalminezcdate) as fyear,month(coalminezcdate) as fmonth")
                ->group(" COALMINEID,COALFIELDID, riqi ")
                //->cache(true,1800)
                ->where($map)
                ->order(" fyear asc,fmonth asc ")->select();
//die(db()->getlastsql());
            $riqi=[];$price=[];$title='发运量';
            foreach($list as $k=>$v)
            {
                $triqi=strtotime($v['riqi']);
                $riqi[]=date("y/m/d",$triqi);
                $price[]=$v['fayun'];
                $title=$v['COALMINENAME'].'-'.$v['FIELDNAME'];
            }

            $res['riqi']=$riqi;
            $res['showdata']=$price;
            $res['title']=$title;









            //dump($price);die("87");
            //$list = collection($list)->toArray();

            return json($res);
        }

        return $this->fetch();
    }


    /**
     * 发运线路
     */
    public function fayunline()
    {
        //当前页
        $page = $this->request->request("pageNumber");
        //分页大小
        $pagesize = $this->request->request("pageSize");




        //$sql=" SELECT  COALMINEID,   COALMINENAME,  COALFIELDID ,FIELDNAME FROM mt_coaltransrec where 1=1  group by COALMINEID,COALFIELDID ;";
       // $list=db()->query($sql);
        $list=db("coaltransrec")->field(" COALMINEID,   COALMINENAME,  COALFIELDID ,FIELDNAME,count(1) as cishu ")
            ->cache(true,3000)
			->where('rktype','QY_XIAOSHOU')
            ->group("COALMINEID,   COALFIELDID ")
            ->order('cishu desc')
            ->select();

        $mylist=[];
        foreach ($list as $k=>$v)
        {
            $id = $v['COALMINEID'] . "-" . $v['COALFIELDID'];
            $tp['id'] = $id;
            $tp['name'] = $v['COALMINENAME'] . '-' . $v['FIELDNAME'].'-'.$v['cishu'];
            $mylist[] = $tp;
        }

        $pagelist=[];
        for($j=($page-1)*$pagesize;$j<$page*$pagesize;$j++)
        {
            $pagelist[]=$mylist[$j];
        }

        $res['list']=$pagelist;
        $res['total']=ceil( count($mylist)/$pagesize);

        // dump($list);
        return json($res);
    }

    public function fayunbing()
    {

        return $this->fetch();
    }


    /**
     * 采购线路
     */
    public function cgline()
    {

        //当前页
        $page = $this->request->request("pageNumber");
        //分页大小
        $pagesize = $this->request->request("pageSize");




        //$sql=" SELECT  COALMINEID,   COALMINENAME,  COALFIELDID ,FIELDNAME FROM mt_coaltransrec where 1=1  group by COALMINEID,COALFIELDID ;";
        // $list=db()->query($sql);
        $list=db("coaltransrec")->field(" COALMINEID,   COALMINENAME,  COALFIELDID ,FIELDNAME,count(1) as cishu ")
            ->cache(true,3000)
            ->where('rktype','QY_CAIGOU')
            ->group("COALMINEID,   COALFIELDID ")
            ->order('cishu desc')
            ->select();

        $mylist=[];
        foreach ($list as $k=>$v)
        {
            $id = $v['COALMINEID'] . "-" . $v['COALFIELDID'];
            $tp['id'] = $id;
            $tp['name'] = $v['COALMINENAME'] . '-' . $v['FIELDNAME'].'-'.$v['cishu'];
            $mylist[] = $tp;
        }

        $pagelist=[];
        for($j=($page-1)*$pagesize;$j<$page*$pagesize;$j++)
        {
            $pagelist[]=$mylist[$j];
        }

        $res['list']=$pagelist;
        $res['total']=ceil( count($mylist)/$pagesize);

        // dump($list);
        return json($res);
    }

    /**
     * 采购数据
    */
    public function cgdata()
    {
        if ($this->request->isAjax()) {

            $luxian=input("xianlu");
            $a=explode("-",$luxian);
            if(count($a)!=2) $this->error("线路错误!");

            $map=[];
            if($a)
            {
                $map['coalmineid']=$a[0];
                $map['coalfieldid']=$a[1];
            }

            $riqi=input("riqi");
            $start=date("Y-m-d",strtotime("-36 month"));
            $end=date("Y-m-d" );
            if($riqi)
            {
                $b=explode(" - ",$riqi);
                if(count($b)!=2) $this->error("日期错误");
                $start=date("Y-m-d",strtotime($b[0]));
                $end=date("Y-m-d",strtotime($b[1]));

                if($b)
                {
                    $map['coalminezcdate']=['between',$b];
                }

            }
            else
            {
                $map['coalminezcdate']=['between',[$start,$end]];
            }

            /*  $list = db("rxzc")->field("COALMINENAME ,RKCOALFIELD,sum(JWEIGHT) as fayun, zcdate as riqi")
                  ->group("  to_days(ZCDATE)")
                  ->where($map)->order("ZCDATE asc  ")
                 //->buildsql();
                  ->select();
                //die($list);

            SELECT   COALMINEID,COALFIELDID,COALMINENAME,FIELDNAME,CONCAT(year(coalminezcdate),'-',month(coalminezcdate) ) as yuefen ,sum(jweight) as fayun ,
  year(coalminezcdate) as fyear,month(coalminezcdate) as fmonth

  from  `mt_coaltransrec`  group by COALMINEID,COALFIELDID, yuefen
  order by fyear asc,fmonth asc


  */
            $map['rktype']='QY_CAIGOU';

            $list = db("coaltransrec")
                ->field("COALMINEID,COALFIELDID,COALMINENAME,FIELDNAME,CONCAT(year(coalminezcdate),'-',month(coalminezcdate) ) as riqi ,sum(jweight) as fayun ,
year(coalminezcdate) as fyear,month(coalminezcdate) as fmonth")
                ->group(" COALMINEID,COALFIELDID, riqi ")
                //->cache(true,1800)
                ->where($map)
                ->order(" fyear asc,fmonth asc ")->select();
//die(db()->getlastsql());
            $riqi=[];$price=[];$title='发运量';
            foreach($list as $k=>$v)
            {
                $triqi=strtotime($v['riqi']);
                $riqi[]=date("y/m/d",$triqi);
                $price[]=$v['fayun'];
                $title=$v['COALMINENAME'].'-'.$v['FIELDNAME'];
            }

            $res['riqi']=$riqi;
            $res['showdata']=$price;
            $res['title']=$title;


            //dump($price);die("87");
            //$list = collection($list)->toArray();

            return json($res);
        }









    }


    public function caigou()
    {
        return $this->fetch();
    }


    /*
     * 天气显示界面
     */
    public function weather()
    {
        $cityList=db("diquwenduset")->field("name")->select();
        $this->assign("cityList",$cityList);
        return $this->fetch();
    }

    /*
     * 温度变化曲线取数据
     */
    public function weatherdata()
    {
        if ($this->request->isAjax()) {

            $params = $this->request->post("row/a");
            $diqu=$params["diqu"];

            $riqi=input("riqi");
            $start=date("Y-m-d",strtotime("-1 month"));
            $end=date("Y-m-d" );
            if($riqi)
            {
                $b=explode(" - ",$riqi);
                if(count($b)!=2) $this->error("日期错误");
                $start=date("Y-m-d",strtotime($b[0]));
                $end=date("Y-m-d",strtotime($b[1]));

                if($b)
                {
                    $map['riqi']=['between',$b];
                }

            }
            else
            {
                $map['riqi']=['between',[$start,$end]];
            }




            $list = db("diquwendu")->where(['district'=>$diqu])
                ->where($map)
                ->select();
            $riqi=[];$price=[];$title='天气走势';
            foreach($list as $k=>$v)
            {
                $triqi=strtotime($v['riqi']);
                $riqi[]=date("y/m/d",$triqi);
                $price[]=$v['temp'];

            }






            $res['riqi']=$riqi;
            $res['showdata']=$price;
            $res['title']=$title;
            return json($res);

        }

    }


    /**
     * 电厂显示界面
     * @return mixed
     */
    public function dcxiaoshou()
    {
    /*    $begintime = strtotime('-2 month');

        $dcid=db("dianchang")->order("id desc")->find();


        $list = db('dcxiaoshou')->where('riqi', 'gt', date('Y-m-d', $begintime))
            ->where("dianchang_id",$dcid)
            ->order('riqi asc')
            ->select();

        $this->view->assign([
            'paylist' => $rihao,
            'createlist' => $xieche,

        ]);*/

        return $this->view->fetch();

    }

    /**
     * 电厂销售数据
     * @return \think\response\Json
     */
    public function dcxiaoshoudata()
    {

     if ($this->request->isAjax()) {

        $dc_id = input("dianchang");
        $map = [];
        if ($dc_id) {
            $map['dianchang_id'] = ['in', $dc_id];
        }

        $riqi = input("riqi");
        $start = date("Y-m-1", strtotime("-1 month"));
        $end = date("Y-m-1", strtotime("+1 month"));
        if ($riqi) {
            $b = explode(" - ", $riqi);
            if (count($b) != 2) $this->error("日期错误");
            $start = date("Y-m-1", strtotime($b[0]));
            $end = date("Y-m-1", strtotime("+1 month", strtotime($b[1])));

            if ($b) {
                $map['riqi'] = ['between', $b];
            }

        } else {
            $map['riqi'] = ['between', [$start, $end]];
        }


        $tongjiar = [];//统计数组
        $riqi = [];
        $start = strtotime($start);
        $end = strtotime($end);
        //初始化每个月的销售值数组为0
        for (; $start <= $end;) {

            $yuefen = date("y/m/1", $start);
            $riqi[] = $yuefen;
            $tongjiar[$yuefen] = 0;//初始化每天的值
            $start = strtotime("+1 month", $start);

        }

        // $diangchansnames=db("dianchang")->select();
        $diangchansnames = db("dianchang")->where("id", "in", $dc_id)->select();
        foreach ($diangchansnames as $key => $value) {
            $showinfo['info'] = $value;
            $showinfo['tongji'] = $tongjiar;
            $dcnames[$value['id']] = $showinfo;
        }


        $list = db("dcxiaoshou")
            ->field("dianchang_id,riqi,xiaoshou")
            ->where($map)
            ->order("riqi asc")
            ->select();

        $dianchangs = [];
        $price = [];
        $title = '电厂销售趋势';
        foreach ($list as $k => $v) {

            $dcid = $v['dianchang_id'];
            $dcinfo = &$dcnames[$dcid];//取出电厂的信息
            if (!$dcinfo) continue;//没有找到电厂的信息,就跳过

            $triqi = date("y/m/1", strtotime($v['riqi']));//统计日期
            $dcinfo['tongji'][$triqi] += $v['xiaoshou'];//当月销售数据


        }
        // dump($dcnames);die("713");


        $tpnum = 0;
        $resshow = [];
        foreach ($dcnames as $key => $dcvalue) {

            //dump($dcvalue);die;
            /**
             *   name:'邮件营销',
             * type:'line',
             * stack: '总量',
             * data:[120, 132, 101, 134, 90, 230, 210]
             */
            $tp = [];
            $tp['name'] = $dcvalue["info"]["name"];
            $tp['type'] = 'line';
            $tp['stack'] = '总量';
            foreach ($dcvalue['tongji'] as $ak => $av) {
                $tpnum += $av;
                $tp['data'][] = $av;
            }


            $resshow[] = $tp;
        }


        $res['riqi'] = $riqi;
        $res['showdata'] = $resshow;
        $res['title'] = $title;
        $res['heji'] = $tpnum;
        return json($res);

       }
	   else
		{
		     $request = Request::instance();
		   echo strtolower($request->action());
		}

    }


    /**
     * 港口报表
     * @return mixed
     */
    public function gangkou()
    {
        $this->assign(
            ["paylist"=>[],'createlist'=>[]]

        );
        return $this->fetch();
    }


    /**
     * 港口报表
     * @return mixed
     */
    public function zhongzhuan()
    {
        $this->assign(
            ["paylist"=>[],'createlist'=>[]]

        );
        return $this->fetch();
    }



    /**
     * 港口销售数据与库存数据
     * @return \think\response\Json
     */
    public function gangkoudata()
    {

        if ($this->request->isAjax()) {

            $dc_id = input("gangkou", 0, 'intval');
            $map = [];
            if ($dc_id) {
                $map['gangkou_id'] = $dc_id;
            }

            $riqi=input("riqi");
            $start=date("Y-m-d",strtotime("-1 month"));
            $end=date("Y-m-d" );
            if($riqi)
            {
                $b=explode(" - ",$riqi);
                if(count($b)!=2) $this->error("日期错误");
                $start=date("Y-m-d",strtotime($b[0]));
                $end=date("Y-m-d",strtotime($b[1]));

                if($b)
                {
                    $map['riqi']=['between',$b];
                }

            }
            else
            {
                $map['riqi']=['between',[$start,$end]];
            }



            $list = db("gangkoukcmj")
                ->field("gangkou_id,riqi,kucun,meijia")
                ->where($map)
                ->order("riqi asc")->select();

            $kucun=[];
            $riqi=[];$price=[];$title='港口库存煤价图';
            foreach($list as $k=>$v)
            {
                $triqi=strtotime($v['riqi']);
                $riqi[]=date("y/m/d",$triqi);

                $kucun[]=$v['kucun'];
             //   $meijia[]=$v['meijia'];

            }

            $fudu=0;
            if($kucun)
            {
                $fudu=max($kucun)-min($kucun);
                $fudu=round($fudu);
            }

            $backdata[]=['name'=>"库存",'lstdata'=>$kucun,'fudu'=>$fudu];
         //   $backdata[]=['name'=>"煤价",'lstdata'=>$meijia];




            $res['riqi']=$riqi;
            $res['title']=$title;
            $res['series'] = $backdata;
            return json($res);

        }

    }



    public function zhongzhuanshichang()
    {
        $this->assign(["paylist"=>[],'createlist'=>[]]);
        return $this->fetch();
    }

    public function zhongzhuandata()
    {


        if ($this->request->isAjax()) {

            $dc_id = input("shichang", 0, 'intval');
            $map = [];
            if ($dc_id) {
                $map['zhongzhuan_id'] = $dc_id;
            }

            $riqi=input("riqi");
            $start=date("Y-m-d",strtotime("-1 month"));
            $end=date("Y-m-d" );
            if($riqi)
            {
                $b=explode(" - ",$riqi);
                if(count($b)!=2) $this->error("日期错误");
                $start=date("Y-m-d",strtotime($b[0]));
                $end=date("Y-m-d",strtotime($b[1]));

                if($b)
                {
                    $map['riqi']=['between',$b];
                }

            }
            else
            {
                $map['riqi']=['between',[$start,$end]];
            }



            $list = db("zhongzhuankc")
                ->field("zhongzhuan_id,riqi,kucun")
                ->where($map)
                ->order("riqi asc")->select();

            $kucun=[];
            $riqi=[];$price=[];$title='中转市场库存图';
            foreach($list as $k=>$v)
            {
                $triqi=strtotime($v['riqi']);
                $riqi[]=date("y/m/d",$triqi);

                $kucun[]=$v['kucun'];
                //   $meijia[]=$v['meijia'];

            }

            $fudu=0;
            if($kucun)
            {
                $fudu=max($kucun)-min($kucun);
                $fudu=round($fudu);
            }

            $backdata[]=['name'=>"库存",'lstdata'=>$kucun,'fudu'=>$fudu];
            //   $backdata[]=['name'=>"煤价",'lstdata'=>$meijia];




            $res['riqi']=$riqi;
            $res['title']=$title;
            $res['series'] = $backdata;
            return json($res);

        }
        else
        {
            $res["msg"]=" 请用post请求数据";
            return json($res);
        }


    }





}
